

<!DOCTYPE html>
<html lang="zh-CN">

<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>MySQL学习（一） - cxp&#39;s blog</title>
  <meta name="apple-mobile-web-app-capable" content="yes" />
  <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
  <meta name="google" content="notranslate" />

  
  
  <meta name="description" content=" 什么是SQL?
SQL(Structed Query..."> 
  
  <meta name="author" content="Alex"> 

  
    <link rel="icon" href="/images/icons/favicon-16x16.png" type="image/png" sizes="16x16">
  
  
    <link rel="icon" href="/images/icons/favicon-32x32.png" type="image/png" sizes="32x32">
  
  
    <link rel="apple-touch-icon" href="/images/icons/apple-touch-icon.png" sizes="180x180">
  
  
    <meta rel="mask-icon" href="/images/icons/stun-logo.svg" color="#333333">
  
  
    <meta rel="msapplication-TileImage" content="/images/icons/favicon-144x144.png">
    <meta rel="msapplication-TileColor" content="#000000">
  

  <link rel="stylesheet" href="/css/style.css">

  
  <link rel="stylesheet" href="//at.alicdn.com/t/font_1445822_h1619vhl1nr.css">
  

  
  
  <link rel="stylesheet" href="https://cdn.bootcss.com/fancybox/3.5.7/jquery.fancybox.min.css">
  

  
  
  <link rel="stylesheet" href="https://cdn.bootcss.com/highlight.js/9.18.1/styles/xcode.min.css">
  

  <script>
    var CONFIG = window.CONFIG || {};
    var ZHAOO = window.ZHAOO || {};
    CONFIG = {
      isHome: false,
      fancybox: true,
      pjax: false,
      lazyload: {
        enable: true,
        loadingImage: '',
      },
      donate: {
        enable: true,
        alipay: 'https://pic.izhaoo.com/alipay.jpg',
        wechat: 'https://pic.izhaoo.com/wechat.jpg'
      },
      motto: {
        api: '',
        default: '我在开了灯的床头下，想问问自己的心啊。'
      },
      galleries: {
        enable: true
      },
      fab: {
        enable: true,
        alwaysShow: false
      },
      carrier: {
        enable: true
      },
      daovoice: {
        enable: true
      }
    }
  </script>

  

  
<link rel="alternate" href="/atom.xml" title="cxp's blog" type="application/atom+xml">
</head>
<body class="lock-screen">
  <div class="loading"></div>
  


<nav class="navbar">
  <div class="left"></div>
  <div class="center">MySQL学习（一）</div>
  <div class="right">
    <i class="iconfont iconmenu j-navbar-menu"></i>
  </div>
</nav>

  <nav class="menu">
  <div class="menu-wrap">
    <div class="menu-close">
      <i class="iconfont iconbaseline-close-px"></i>
    </div>
    <ul class="menu-content">
      
      
      
      
      <li class="menu-item"><a href="/ " class="underline"> 首页</a></li>
      
      
      
      
      <li class="menu-item"><a href="/galleries " class="underline"> 摄影</a></li>
      
      
      
      
      <li class="menu-item"><a href="/archives " class="underline"> 归档</a></li>
      
      
      
      
      <li class="menu-item"><a href="/tags " class="underline"> 标签</a></li>
      
      
      
      
      <li class="menu-item"><a href="/categories " class="underline"> 分类</a></li>
      
      
      
      
      <li class="menu-item"><a href="/about " class="underline"> 关于</a></li>
      
    </ul>
    <div class="menu-copyright"><p>Powered by <a target="_blank" href="https://hexo.io">Hexo</a>  |  Theme - <a target="_blank" href="https://github.com/izhaoo/hexo-theme-zhaoo">zhaoo</a></p></div>
  </div>
</nav>
  <main id="main">
  <div class="container" id="container">
    <article class="article">
  <div class="wrap">
    <section class="head">
  <img   class="lazyload" data-original="/images/theme/post-image.jpg" src=""  draggable="false">
  <div class="head-mask">
    <h1 class="head-title">MySQL学习（一）</h1>
    <div class="head-info">
      <span class="post-info-item"><i class="iconfont iconcalendar"></i>三月 26, 2019</span
        class="post-info-item">
      
      <span class="post-info-item"><i class="iconfont iconfont-size"></i>2829</span>
    </div>
  </div>
</section>
    <section class="main">
      <section class="content">
        <h1 id="什么是sql"><a class="markdownIt-Anchor" href="#什么是sql"></a> 什么是SQL?</h1>
<p>SQL(Structed Query Language)是一种<code>访问</code>和<code>处理</code>数据库的标准的计算机语言。</p>
<ul>
<li>SQL 面向数据库执行查询</li>
<li>SQL 可从数据库取回数据</li>
<li>SQL 可在数据库中插入新的记录</li>
<li>SQL 可更新数据库中的数据</li>
<li>SQL 可从数据库删除记录</li>
<li>SQL 可创建新数据库</li>
<li>SQL 可在数据库中创建新表</li>
<li>SQL 可在数据库中创建存储过程</li>
<li>SQL 可在数据库中创建视图</li>
<li>SQL 可以设置表、存储过程和视图的权限</li>
</ul>
<h1 id="什么是mysql"><a class="markdownIt-Anchor" href="#什么是mysql"></a> 什么是MySQL?</h1>
<p>是一种关系型数据库。可以按照数据结构来组织、存储数据的仓库。是一个免费开源项目，功能强大。</p>
<h1 id="准备工作"><a class="markdownIt-Anchor" href="#准备工作"></a> 准备工作</h1>
<p>首先是怎么安装，在安装之后开始使用，这段时间使用Linux，所以在Linux里面开始搭建使用。</p>
<ol>
<li>
<p>虚拟机安装CentOS 7</p>
<ul>
<li>安装VMware</li>
<li>创建一个虚拟机，然后进行按步骤进行安装，在选项中要记得设置好root密码，选择最小安装就可以。</li>
</ul>
</li>
<li>
<p>安装MySQL</p>
</li>
</ol>
<h1 id="mysql的基本操作"><a class="markdownIt-Anchor" href="#mysql的基本操作"></a> MySQL的基本操作</h1>
<ol>
<li>
<p>创建数据库</p>
<ul>
<li>登录MySQL服务器后，使用<code>crate database &lt;name&gt;</code>；</li>
<li>使用mysqladmin， <code>mysqladmin u root -p create &lt;name&gt;</code>；</li>
</ul>
</li>
<li>
<p>删除数据库</p>
<ul>
<li>同1中，使用<code>drop database &lt;name&gt;</code>；</li>
<li>同1中，使用<code>mysqladmin -u root -p drop &lt;name&gt;</code>；</li>
</ul>
</li>
<li>
<p>选择数据库</p>
<ul>
<li><code>use &lt;name&gt;</code>；</li>
</ul>
</li>
<li>
<p>创建数据库表</p>
<p>创建数据表需要：</p>
<ul>
<li>表名</li>
<li>表字段名</li>
<li>定义每个表字段</li>
</ul>
<p>语法：注意MySQL命令终止符为<code>;</code></p>
<ul>
<li>CREATE TABLE &lt;TABLE_NAME&gt;()ENGINE=InnoDB DEFAULT CHARSET=utf8；</li>
</ul>
</li>
<li>
<p>删除数据表</p>
<ul>
<li>DROP TABLE &lt;TABLE_NAME&gt;；</li>
</ul>
</li>
<li>
<p>插入数据</p>
<ul>
<li>INSERT INTO TABLE_NAME(&lt;,&gt;VALUES&lt;,&gt;);    如果一条语句太长，可以通过enter键来创建新行编写</li>
</ul>
</li>
<li>
<p>查询数据</p>
<ul>
<li>SELECT column_name,column_name FROM TABLE_NAME [WHERE Clause] [LIMINT N][OFFSET M];</li>
</ul>
</li>
<li>
<p>WHERE子句</p>
<ul>
<li>… WHERE condition1 [AND][OR] condition2, …；</li>
<li>注意：<strong>子句中的字符串是不区分大小写的，要想区分开来需要添加WHERE BINARY关键字来限定</strong></li>
<li>可以使用操作符来判断。如<code>&gt;,&lt;,!=,&lt;=,&gt;=,</code></li>
</ul>
</li>
<li>
<p>使用UPDATE查询</p>
<ul>
<li>UPDATE TABLE_NAME SET field1=new-value1,…；</li>
<li>可以同时更新多个字符段，后面可添加WHERE子句</li>
</ul>
</li>
<li>
<p>使DELETE</p>
<ul>
<li>DELETE FROM TABLE_NAME [WHERE Clause]；</li>
<li>其中WHERE子句可以实现制定条件的删除，否则就是表中的全部数据都被删除</li>
</ul>
</li>
<li>
<p>LIKE子句</p>
<ul>
<li>SELECT field1,field2,… FROM TABLE_NAME WHERE … LIKE …；</li>
<li>可以在WHERE中使用LIKE子句，可以使用LIKE 代替=</li>
<li>使用%来代替任意字符，类似于正则表达式中的<code>*</code></li>
</ul>
</li>
<li>
<p>UNION操作符</p>
<blockquote>
<p>操作符用于连接SELECT语句的结果组合到一个结果集合中。多个SELECT语句会删除重复的数据。</p>
</blockquote>
<ul>
<li>SELECT expression1,expression2,… FROM TABLE_NAME [WHERE Clause] UNION [ALL|DISTINCT] SELECT …；</li>
<li>ALL代表返回所有结果集，包含重复数据</li>
</ul>
</li>
<li>
<p>排序ORDER BY子句</p>
<ul>
<li>SELECT field1, field2,…fieldN table_name1, table_name2… ORDER BY field1, [field2…] [ASC [DESC]]；</li>
<li>可以通过设置ASC DESC来设置升序还是降序</li>
</ul>
</li>
<li>
<p>分组</p>
<ul>
<li>SELECT column_name, function(column_name) FROM TABLE_NAME WHERE … GROUP BY column_name;</li>
<li>使用WITH ROOLUP可以实现分组统计数据基础上再进行相同的统计SELECT column_name, function(column_name) FROM TABLE_NAME WHERE … GROUP BY column_name WITH ROLLUP;</li>
<li>使用coalesce设置一个取代NULL 的名称coalesce(a,b,C)若a为NULL则使用b,以此类推，若全为空则设置为NULL</li>
</ul>
</li>
<li>
<p>数据表的连接</p>
<ul>
<li>INNER JOIN 内连接，获取两个表中相互匹配的字段</li>
<li>LEFT JOIN 左连接，获取左边表的全部数据，不管有没有匹配</li>
<li>RIGHT JOIN 右连接，类似上</li>
<li>SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author=b.runoob_author;</li>
</ul>
</li>
<li>
<p>事务</p>
<blockquote>
<p>主要用于处理操作两大，复杂度高的数据，一系列的数据库操作语句构成了事务，在MySQL中只有使用了InnoDB引擎的数据库或者表才支持事务，事务处理是为了维护数据库的完整性，要不进行完成彻底，要不就不变动。</p>
</blockquote>
<p>事务满足四个条件（ACID）:</p>
<ul>
<li>原子性：要么完成，要么不完成</li>
<li>一致性：事务完成前后数据库的完整性不会打破</li>
<li>隔离性：允许并发事务的同时对数据进行操作</li>
<li>持久性：事务结束之后，对数据的修改是永久的</li>
</ul>
<p>事务的执行流程：</p>
<ul>
<li>BEGIN;</li>
<li>ROLLBAK;</li>
<li>COMMIT;</li>
<li>可以在提交之前任意点回滚到开始事务前，可以通过SET AUTOCOMMIT=0或1来设置是否自动提交</li>
</ul>
</li>
<li>
<p>ALTER变更</p>
<blockquote>
<p>当需要变更表中的字段或者修改表名时使用ALTER语句</p>
</blockquote>
<ul>
<li>ALTER TABLE TABLE_NAME DROP/ADD FIRST/AFTER I TYPE;</li>
<li>ALTER TABLE TABLE_NAME RENAME TO NEW_NANE;</li>
<li>变更存储引擎；删除外键别名；</li>
</ul>
</li>
<li>
<p>索引</p>
<blockquote>
<p>索引可以提高数据库的检索速度，但是学数据结构的时候我们知道数组随机存取很快但是更新的速度相对会慢，索引也一样。</p>
</blockquote>
<p>索引可以分为：</p>
<ul>
<li>单列索引：一个索引包含一个列。</li>
<li>组合索引：一个索引包含多个列。</li>
</ul>
</li>
</ol>
<blockquote>
<p>待补充</p>
</blockquote>

      </section>
      <section class="extra">
        
        <ul class="copyright">
  
  <li><strong>本文作者：</strong>Alex</li>
  <li><strong>本文链接：</strong><a href="https://cxpeng.cn/archives/42250562.html">https://cxpeng.cn/archives/42250562.html</a></li>
  <li><strong>版权声明：</strong>本博客所有文章均采用<a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh"
      rel="external nofollow" target="_blank"> BY-NC-SA </a>许可协议，转载请注明出处！</li>
  
</ul>
        
        
        <section class="donate">
  <div class="qrcode">
    <img   class="lazyload" data-original="https://pic.izhaoo.com/alipay.jpg" src="" >
  </div>
  <div class="icon">
    <a href="javascript:;" target="_blank" rel="noopener" id="alipay"><i class="iconfont iconalipay"></i></a>
    <a href="javascript:;" target="_blank" rel="noopener" id="wechat"><i class="iconfont iconwechat-fill"></i></a>
  </div>
</section>
        
        
  <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/tags/SQL/" rel="tag">SQL</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag">数据库</a></li></ul>

        
<nav class="nav">
  
    <a href="/archives/7fd01c5b.html"><i class="iconfont iconleft"></i>怎么样使用Git？</a>
  
  
    <a href="/archives/843f592a.html">Linux基础（一）<i class="iconfont iconright"></i></a>
  
</nav>

      </section>
      
      <section class="comments">
  
  <div class="btn" id="comments-btn">查看评论</div>
  
  
</section>
      
    </section>
  </div>
</article>
  </div>
</main>
  <footer class="footer">
  <div class="footer-social">
    
    
    
    
    
    <a href="tencent://message/?Menu=yes&uin=894519210 " target="_blank" onMouseOver="this.style.color= '#12B7F5'"
      onMouseOut="this.style.color='#33333D'">
      <i class="iconfont footer-social-item  iconQQ "></i>
    </a>
    
    
    
    
    
    <a href="javascript:; " target="_blank" onMouseOver="this.style.color= '#09BB07'"
      onMouseOut="this.style.color='#33333D'">
      <i class="iconfont footer-social-item  iconwechat-fill "></i>
    </a>
    
    
    
    
    
    <a href="https://www.instagram.com/izhaoo/ " target="_blank" onMouseOver="this.style.color= '#DA2E76'"
      onMouseOut="this.style.color='#33333D'">
      <i class="iconfont footer-social-item  iconinstagram "></i>
    </a>
    
    
    
    
    
    <a href="https://github.com/izhaoo " target="_blank" onMouseOver="this.style.color= '#24292E'"
      onMouseOut="this.style.color='#33333D'">
      <i class="iconfont footer-social-item  icongithub-fill "></i>
    </a>
    
    
    
    
    
    <a href="mailto:izhaoo@163.com " target="_blank" onMouseOver="this.style.color='#FFBE5B'"
      onMouseOut="this.style.color='#33333D'">
      <i class="iconfont footer-social-item  iconmail"></i>
    </a>
    
  </div>
  <div class="footer-copyright"><p>Powered by <a target="_blank" href="https://hexo.io">Hexo</a>  |  Theme - <a target="_blank" href="https://github.com/izhaoo/hexo-theme-zhaoo">zhaoo</a></p></div>
</footer>
  
      <div class="fab fab-plus">
    <i class="iconfont iconplus"></i>
  </div>
  
  <div class="fab fab-daovoice">
    <i class="iconfont iconcomment"></i>
  </div>
  
  <div class="fab fab-up">
    <i class="iconfont iconcaret-up"></i>
  </div>
  
<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
        tex2jax: {
            inlineMath: [ ["$","$"], ["\\(","\\)"] ],
            skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code'],
            processEscapes: true
        }
    });
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax();
        for (var i = 0; i < all.length; ++i)
            all[i].SourceElement().parentNode.className += ' has-jax';
    });
</script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js?config=TeX-MML-AM_CHTML"></script>
</body>

<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.min.js"></script>




<script src="https://cdn.bootcdn.net/ajax/libs/jquery.lazyload/1.9.1/jquery.lazyload.min.js"></script>




<script src="https://cdn.bootcss.com/fancybox/3.5.7/jquery.fancybox.min.js"></script>




<script src="/js/utils.js"></script>
<script src="/js/modules.js"></script>
<script src="/js/zui.js"></script>
<script src="/js/script.js"></script>




<script>
  (function (i, s, o, g, r, a, m) {
    i["DaoVoiceObject"] = r;
    i[r] = i[r] || function () {
      (i[r].q = i[r].q || []).push(arguments)
    }, i[r].l = 1 * new Date();
    a = s.createElement(o), m = s.getElementsByTagName(o)[0];
    a.async = 1;
    a.src = g;
    a.charset = "utf-8";
    m.parentNode.insertBefore(a, m)
  })(window, document, "script", ('https:' == document.location.protocol ? 'https:' : 'http:') +
    "//widget.daovoice.io/widget/0f81ff2f.js", "daovoice")
  daovoice('init', {
    app_id: "abcdefg"
  }, {
    launcher: {
      disableLauncherIcon: true,
    },
  });
  daovoice('update');
</script>



<script>
  (function () {
    var bp = document.createElement('script');
    var curProtocol = window.location.protocol.split(':')[0];
    if (curProtocol === 'https') {
      bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
    } else {
      bp.src = 'http://push.zhanzhang.baidu.com/push.js';
    }
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(bp, s);
  })();
</script>


<script>
  var _hmt = _hmt || [];
  (function () {
    var hm = document.createElement("script");
    hm.src = "https://hm.baidu.com/hm.js?4c204d8bc027a0455b5fc642ac334ca8";
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(hm, s);
  })();
</script>










</html>